package cc.jcizp;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class MergeExcel {

	public static void export(File file1,File file2,OutputStream out,String key) throws Exception{
		List<?> list1 = getList(file1);
		List<?> list2 = getList(file2);
		List<?> merge = mergeList(list1,list2,key);
		exportExcel("总表", merge, out);
		out.close();
		
	}
	
	//存在问题
	private static List<Map<String,Object>> getList(File file) throws Exception{
		String fileName = file.getName();
		FileInputStream fileInputStream = null;
		Workbook wb = null;
		try{
			fileInputStream = new FileInputStream(file);
			if(fileName.endsWith("xls")){
				wb = new HSSFWorkbook(fileInputStream);
			}else if(fileName.endsWith("xlsx")){
				wb = new XSSFWorkbook(fileInputStream);
			}else{
				System.out.println(fileName+"不是excel文件！");
				return null;
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(fileInputStream != null){
				fileInputStream.close();
			}
			if(wb != null){
				wb.close();
			}
		}
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    	List<String> headerList = new ArrayList<String>();
		try{
	    	Sheet sheet = wb.getSheetAt(0);
	    	Row header = sheet.getRow(0);
	    	for(int i = 0 ; i < header.getLastCellNum(); i++){
	    		String s = header.getCell(i).toString().trim();
	    		headerList.add(s);
	    	}
	    	
	    	//内存泄漏
	    	for(int j = 0 ; j < sheet.getLastRowNum()+1;j++){
				//创建一个行对象
				Row row = sheet.getRow(j);
				
				Map<String, Object> map = new HashMap<String, Object>();
				for(int i = 0 ; i < row.getLastCellNum() ; i++){
					//创建一个行里的一个字段对象，即单元格中的值
					Cell cell = row.getCell(i);
					String metadata = cell.toString();
					if(metadata==null||metadata.equals("")){
						continue;
					}
					map.put(headerList.get(i), metadata.trim());
					
				}
				list.add(map);
			}
	    	
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(fileInputStream != null){
	    		fileInputStream.close();
	    	}
	    	if(wb != null){
	    		wb.close();
	    	}
		}
		
		return list;
    }  
	
	private static List<Map<String, Object>> mergeList(List<?> list1,List<?> list2,String key){
		List<Map<String, Object>> list = new ArrayList<>();
		if(list1.size()>0&&list2.size()>0){
			Map<String, Object> m = (Map<String, Object>) list1.get(0);
			Map<String, Object> m1 = (Map<String, Object>) list2.get(0);
			m.putAll(m1);
			list.add(m);
		}else{
			return null;
		}
		try{
			for(int i = 1 ; i < list1.size() ; i++){
				Map<String, Object> m1 = (Map<String, Object>) list1.get(i);
				for(int j = 1 ; j < list2.size() ; j++){
					Map<String, Object> m2 = (Map<String, Object>) list2.get(j);
					if(m1.get(key).equals(m2.get(key))){
						m1.putAll(m2);
						break;
					}
				}
				list.add(m1);
			}
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		
		return list;
	}
	
	
	private static void exportExcel(String title,List<?> mapList,OutputStream out){
        //声明一个工作簿  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        //生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title);  
        //设置表格默认列宽度为15个字符  
        sheet.setDefaultColumnWidth(20);
        //产生表格标题行  
        HSSFRow row = sheet.createRow(0);  
        //获取标题行
        Map<String,Object> headers = (Map<String, Object>) mapList.get(0); 
        Set keySet = headers.keySet(); // key的set集合  
        Iterator it = keySet.iterator();  
        List header = new ArrayList<>();
        while(it.hasNext()){  
            Object k = it.next(); // key  
            Object v = headers.get(k);  //value       
            header.add(String.valueOf(v));
        }
        for (int i=0;i<mapList.size();i++) {  
            Map<String,Object> map = (Map<String, Object>) mapList.get(i);  
            row = sheet.createRow(i);  
            for(int j = 0 ; j < header.size() ; j++){
            	Object v = map.get(header.get(j)); 
            	row.createCell(j).setCellValue(String.valueOf(v));  
            }
        }  
        try {  
            workbook.write(out);  
        } catch (IOException e) {  
            e.printStackTrace();  
        }finally{
        	try {
				workbook.close();
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
        }
    }

}
